﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Esint.Common.Data;
using Esint.JudicialAuction.Model;
using Esint.JudicialAuction.IDAL;
using Esint.Common.Model;
using System.Data.SqlClient;
using Esint.Common;
using Esint.JudicialAuction.Model.Schedule;

namespace Esint.JudicialAuction.SqlDAL
{
    /// <summary>
    /// 模块名称： 数据访问层
    /// 作    者：谷明
    /// 生成日期：2020年03月29日
    /// 生成模板: Esint.Template.SqlDAL.SqlDAL_11 版
    ///           适用于工厂模式架构
    /// 修改说明：
    /// </summary>
    public partial class Schedule_ShiftsData : BaseData, ISchedule_ShiftsData
    {
        /// <summary>
        /// 根据查询条件查询排班记录
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="place"></param>
        /// <param name="workType"></param>
        /// <returns></returns>
        public List<Schedule_ShiftsInfo> GetScheduleList(string startTime, string endTime, string place, string workType)
        {
            DataQuery query = new DataQuery();
            query.SQLText = @"select *,(select u.UserName from T_Schedule_User u where u.UserId=T_Schedule_Shifts.UserId )UserName 
,(select u.Gender from T_Schedule_User u where u.UserId=T_Schedule_Shifts.UserId )UserGender
from T_Schedule_Shifts where 1=1";

            if (!string.IsNullOrWhiteSpace(startTime))
            {
                query.SQLText += " and WorkDate>=@startTime";
                query.WhereParameters.Add(new WhereParameter("@startTime", startTime));
            }
            if (!string.IsNullOrWhiteSpace(endTime))
            {
                query.SQLText += " and WorkDate<=@endTime";
                query.WhereParameters.Add(new WhereParameter("@endTime", endTime));
            }
            if (!string.IsNullOrWhiteSpace(place))
            {
                query.SQLText += " and WorkPlaceCode=@WorkPlaceCode";
                query.WhereParameters.Add(new WhereParameter("@WorkPlaceCode", place));
            }
            if (!string.IsNullOrWhiteSpace(workType))
            {
                query.SQLText += " and WorkTypeCode=@WorkTypeCode";
                query.WhereParameters.Add(new WhereParameter("@WorkTypeCode", workType));
            }

            return GetModelList<Schedule_ShiftsInfo>(query);
        }

        /// <summary>
        /// 保存一次工作列表
        /// </summary>
        /// <param name="onceWorks"></param>
        public void SaveOnceWorkList(List<OnceWork> onceWorks)
        {
            IDbTransaction trans = basicDataAccess.GetTransaction();
            try
            {
                foreach (var once in onceWorks)
                {
                    SaveOnceWork(once, trans);
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        public void SaveOnceWork(OnceWork onceWork, IDbTransaction trans)
        {
            DeleteScheduleByDate(onceWork, trans);

            foreach (var item in onceWork.ScheduleList)
            {
                try
                {
                    Insert(item, trans);
                }
                catch (Exception ex)
                {
                    CommonExtend.ErrorLog(ex, item.ToString());
                    throw ex;
                }
            }

            if (onceWork.PersonList != null && onceWork.PersonList.Count > 0)
            {
                var userDal = new Schedule_UserData();
                foreach (var item in onceWork.PersonList)
                {
                    userDal.Update(item, trans);
                }
            }
        }

        /// <summary>
        /// 保存排班结果的方法
        /// </summary>
        /// <param name="ScheduleList"></param>
        /// <param name="UserList"></param>
        public void SaveScheduleAndUser(OnceWork onceWork)
        {
            var trans = basicDataAccess.GetTransaction();

            try
            {
                DeleteScheduleByDate(onceWork, trans);

                foreach (var item in onceWork.ScheduleList)
                {
                    try
                    {
                        Insert(item, trans);
                    }
                    catch (Exception ex)
                    {
                        CommonExtend.ErrorLog(ex, item.ToString());
                        throw ex;
                    }
                }

                if (onceWork.PersonList != null && onceWork.PersonList.Count > 0)
                {
                    Schedule_UserData userDal = new Schedule_UserData();
                    foreach (var item in onceWork.PersonList)
                    {
                        userDal.Update(item, trans);
                    }
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                
                throw ex;
            }
        }
        
        /// <summary>
        /// 取在时间范围内的排班最大日期和最小日期
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<DateTime> GetMaxAndMinRecord(string startTime, string endTime)
        {
            DataQuery query = new DataQuery();
            query.SQLText = @"select max(WorkDate) maxdate, min(WorkDate) mindate from T_Schedule_Shifts where 1=1 ";
            if (!string.IsNullOrWhiteSpace(startTime)) {
                query.SQLText += " and WorkDate>=@startTime ";
                query.WhereParameters.Add(new WhereParameter("@startTime", startTime));
            }
            if (!string.IsNullOrWhiteSpace(endTime)) {
                query.SQLText += " and WorkDate<=@endTime ";
                query.WhereParameters.Add(new WhereParameter("@endTime", endTime));
            }

            var dt = ExecuteDataTable(query);

            List<DateTime> list = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                if (!Convert.IsDBNull(dt.Rows[0][0]))
                {
                    list = new List<DateTime>();
                    DateTime maxDate = Convert.ToDateTime(dt.Rows[0][0]);
                    DateTime minDate = Convert.ToDateTime(dt.Rows[0][1]);
                    list.Add(maxDate);
                    list.Add(minDate);
                }
                else
                {
                    return null;
                }
            }

            return list;
        }

        /// <summary>
        /// 获得用户个人的分页排班列表
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public ReturnTable GetScheduleListByUserId(string userId, int pageIndex, int pageSize, string startTime, string endTime)
        {
            DataQuery query = new DataQuery();
            query.SQLText = @"SELECT t.[UserId]
      ,WorkDate
      ,WorkPlaceCode
      ,CONVERT(VARCHAR(10),t.[WorkDate],120)[WorkDateStr]
      ,(select c.meaning from T_Sys_Code c where c.flag=1 and c.code=t.WorkTypeCode)[WorkTypeMeaning]
      ,(select c.meaning from T_Sys_Code c where c.flag=2 and c.code=t.WorkPlaceCode)[WorkPlaceMeaning]
  FROM [T_Schedule_Shifts] t where t.UserId=@UserId";

            query.WhereParameters.Add(new WhereParameter("@UserId", userId));

            if (startTime.IsNotNullOrEmpty())
            {
                query.SQLText += " and t.WorkDate>=@startTime ";
                query.WhereParameters.Add(new WhereParameter("@startTime", startTime));
            }
            if (endTime.IsNotNullOrEmpty())
            {
                query.SQLText += " and t.WorkDate<=@endTime ";
                query.WhereParameters.Add(new WhereParameter("@endTime", endTime));
            }
            query.OrderByString += " WorkDate desc, WorkPlaceCode ";

            var split = new PageSplit() { IsPageView = true, PageIndex = pageIndex, PageSize = pageSize };
            query.PageView = split;
            
            var rt = new ReturnTable();
            rt.Table =ExecuteDataTable(query);
            rt.PageInfo = query.PageView;

            return rt;
        }

        /// <summary>
        /// 删除一个时间段内的中心排班记录
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        public void DeleteScheduleByTimeSpan(string startTime, string endTime, string place)
        {
            var trans = basicDataAccess.GetTransaction();

            try
            {
                //查询涉及到的用户列表
                DataQuery queryUser = new DataQuery();
                string placeWhere = string.Empty;
                if (!string.IsNullOrWhiteSpace(place))
                {
                    placeWhere = " and ts.WorkPlaceCode = @WorkPlaceCode ";
                    queryUser.WhereParameters.Add(new WhereParameter("@WorkPlaceCode", place));
                }

                queryUser.SQLText = $@"update T_Schedule_User 
 set Priority=Priority-(select COUNT(1) from T_Schedule_Shifts ts where ts.UserId=UserId and ts.WorkDate>=@startTime and ts.WorkDate<=@endTime {placeWhere})
,set ScheduleWeight=ScheduleWeight-(select COUNT(1) from T_Schedule_Shifts ts where ts.UserId=UserId and ts.WorkDate>=@startTime and ts.WorkDate<=@endTime {placeWhere})
 where UserId in (select distinct ts.UserId from T_Schedule_Shifts ts where ts.WorkDate>=@startTime and ts.WorkDate<=@endTime {placeWhere} )";
                queryUser.WhereParameters.Add(new WhereParameter("@startTime", startTime));
                queryUser.WhereParameters.Add(new WhereParameter("@endTime", endTime));
                OperateObject operateUser = new OperateObject(queryUser);
                ExecuteNonQuery(operateUser,trans);

                DataQuery query = new DataQuery();

                string workPlaceWhere = string.Empty;
                if (!string.IsNullOrWhiteSpace(place))
                {
                    workPlaceWhere = " and WorkPlaceCode = @WorkPlaceCode ";
                    query.WhereParameters.Add(new WhereParameter("@WorkPlaceCode", place));
                }

                query.SQLText = $"delete from T_Schedule_Shifts where WorkDate>=@startTime and WorkDate<=@endTime {workPlaceWhere}";
                query.WhereParameters.Add(new WhereParameter("@startTime", startTime));
                query.WhereParameters.Add(new WhereParameter("@endTime", endTime));
                OperateObject operateShifts = new OperateObject(query);
                ExecuteNonQuery(operateShifts, trans);
                
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            
        }

        /// <summary>
        /// 删除某次排班的方法
        /// </summary>
        /// <param name="date"></param>
        public void DeleteScheduleByDate(OnceWork onceWork, IDbTransaction transaction)
        {
            try
            {
                DeleteOperHandleUserPriority(onceWork, transaction);

                DataQuery query = new DataQuery();
                query.SQLText = "delete from T_Schedule_Shifts where WorkDate=@WorkDate and WorkPlaceCode=@WorkPlaceCode and WorkTypeCode=@WorkTypeCode";
                query.WhereParameters.Add(new WhereParameter("@WorkDate", onceWork.WorkDateStr));
                query.WhereParameters.Add(new WhereParameter("@WorkPlaceCode", (int)onceWork.WorkPlace));
                query.WhereParameters.Add(new WhereParameter("@WorkTypeCode", (int)onceWork.WorkType));
                OperateObject opobj = new OperateObject(query);
                if (transaction == null)
                {
                    ExecuteNonQuery(opobj);
                }
                else
                {
                    ExecuteNonQuery(opobj, transaction);
                }
            }
            catch (Exception ex)
            {
                if (transaction != null) transaction.Rollback();
                throw ex;
            }
        }

        /// <summary>
        /// 删除用户工作安排的时候，相应的减少排班次数
        /// </summary>
        /// <param name="onceWork"></param>
        /// <param name="transaction"></param>
        public void DeleteOperHandleUserPriority(OnceWork onceWork, IDbTransaction transaction)
        {
            try
            {
                DataQuery query = new DataQuery();
                query.SQLText = "select * from T_Schedule_User where UserId in (select UserId from T_Schedule_Shifts where WorkDate=@WorkDate and WorkPlaceCode=@WorkPlaceCode and WorkTypeCode=@WorkTypeCode)";
                
                query.WhereParameters.Add(new WhereParameter("@WorkDate", onceWork.WorkDateStr));
                query.WhereParameters.Add(new WhereParameter("@WorkPlaceCode", (int)onceWork.WorkPlace));
                query.WhereParameters.Add(new WhereParameter("@WorkTypeCode", (int)onceWork.WorkType));
                List<Schedule_UserInfo> schedule_Users = GetModelList<Schedule_UserInfo>(query, transaction);

                if (schedule_Users.Count > 0)
                {
                    Schedule_UserData userDal = new Schedule_UserData();
                    foreach (var user in schedule_Users)
                    {
                        if (user.UserId == "xxx") continue;

                        user.Priority--;
                        user.ScheduleWeight--;
                        if (transaction != null)
                        {
                            userDal.Update(user, transaction);
                        }
                        else {
                            userDal.Update(user);
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                if (transaction != null) transaction.Rollback();
                throw ex; 
            }
        }

        /// <summary>
        /// 检查换班合法性，并返回不合法的记录
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="targetWork"></param>
        public List<Schedule_ShiftsInfo> CheckSwitchTargetRight(string userId, OnceWork targetWork)
        {
            string targetPrevSql = string.Empty;
            string targetNextSql = string.Empty;

            //查询目标班次前2班逻辑
            if (targetWork.WorkType == WorkType.Daylight)
            {
                targetPrevSql = @"select *
,(select u.UserName from T_Schedule_User u where u.UserId=T_Schedule_Shifts.UserId)UserName
,(select c.meaning from T_Sys_Code c where c.flag=1 and c.code=T_Schedule_Shifts.WorkTypeCode)WorkTypeMeaning
,(select c.meaning from T_Sys_Code c where c.flag=2 and c.code=T_Schedule_Shifts.WorkPlaceCode)WorkPlaceMeaning
from T_Schedule_Shifts where WorkDate=@prevDate and UserId=@UserId";
                targetNextSql = @"select *
,(select u.UserName from T_Schedule_User u where u.UserId=T_Schedule_Shifts.UserId)UserName
,(select c.meaning from T_Sys_Code c where c.flag=1 and c.code=T_Schedule_Shifts.WorkTypeCode)WorkTypeMeaning
,(select c.meaning from T_Sys_Code c where c.flag=2 and c.code=T_Schedule_Shifts.WorkPlaceCode)WorkPlaceMeaning
from T_Schedule_Shifts where ((WorkDate=@nextDate and WorkTypeCode=1) or (WorkDate=@WorkDate)) and UserId=@UserId";
            }
            else
            {
                targetPrevSql = @"select * 
,(select u.UserName from T_Schedule_User u where u.UserId=T_Schedule_Shifts.UserId)UserName
,(select c.meaning from T_Sys_Code c where c.flag=1 and c.code=T_Schedule_Shifts.WorkTypeCode)WorkTypeMeaning
,(select c.meaning from T_Sys_Code c where c.flag=2 and c.code=T_Schedule_Shifts.WorkPlaceCode)WorkPlaceMeaning
from T_Schedule_Shifts where ((WorkDate=@prevDate and WorkTypeCode=2) or (WorkDate=@WorkDate)) and UserId=@UserId";
                targetNextSql = @"select * 
,(select u.UserName from T_Schedule_User u where u.UserId=T_Schedule_Shifts.UserId)UserName
,(select c.meaning from T_Sys_Code c where c.flag=1 and c.code=T_Schedule_Shifts.WorkTypeCode)WorkTypeMeaning
,(select c.meaning from T_Sys_Code c where c.flag=2 and c.code=T_Schedule_Shifts.WorkPlaceCode)WorkPlaceMeaning
from T_Schedule_Shifts where WorkDate=@nextDate and UserId=@UserId";
            }

            DataQuery query = new DataQuery();
            query.SQLText = $"{targetPrevSql} union all {targetNextSql}";
            query.WhereParameters.Add(new WhereParameter("@prevDate", targetWork.WorkDate.AddDays(-1).ToString("yyyy-MM-dd")));
            query.WhereParameters.Add(new WhereParameter("@WorkDate", targetWork.WorkDate.ToString("yyyy-MM-dd")));
            query.WhereParameters.Add(new WhereParameter("@nextDate", targetWork.WorkDate.AddDays(1).ToString("yyyy-MM-dd")));
            query.WhereParameters.Add(new WhereParameter("@UserId", userId));

            return GetModelList<Schedule_ShiftsInfo>(query);
        }

        /// <summary>
        /// 执行换班操作
        /// </summary>
        /// <param name="switchOnceWork"></param>
        public void SaveSwitchWork(SwitchOnceWork switchOnceWork)
        {
            IDbTransaction trans = basicDataAccess.GetTransaction();
            try
            {
                var obj1 = GetSchedule_ShiftsInfo(switchOnceWork.Person1UserId, switchOnceWork.Person1OldWork.WorkDate, ((int)switchOnceWork.Person1OldWork.WorkType).ToString(), trans);
                var obj2 = GetSchedule_ShiftsInfo(switchOnceWork.Person2UserId, switchOnceWork.Person2OldWork.WorkDate, ((int)switchOnceWork.Person2OldWork.WorkType).ToString(), trans);

                Delete(obj1.UserId, obj1.WorkDate, obj1.WorkTypeCode, trans);
                Delete(obj2.UserId, obj2.WorkDate, obj2.WorkTypeCode, trans);

                obj1.UserId = switchOnceWork.Person2UserId;
                obj2.UserId = switchOnceWork.Person1UserId;

                Insert(obj1, trans);
                Insert(obj2, trans);

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
    }
}
